#include "sql_mgr.h"

Sql_Mgr* Sql_Mgr::instance = nullptr;

Sql_Mgr::Sql_Mgr()
{
    init();
}

Sql_Mgr::~Sql_Mgr()
{
    m_Db.close();
}

void Sql_Mgr::test()
{
    qDebug()<<"test";

}

void Sql_Mgr::init()
{
    m_Db = QSqlDatabase::addDatabase("QSQLITE");
    m_Db.setDatabaseName("C:/Users/ZY/Desktop/book.db");

    qDebug()<<m_Db.open();

}

//登录
bool Sql_Mgr::login(QString strUser, QString strPass, int& userId)
{

    QSqlQuery q(m_Db);
    QString strSql = QString("select * from user where username='%1' and password='%2'").arg(strUser).arg(strPass);

    //qDebug()<<strSql;
    bool ret = q.exec(strSql);
    if(!q.next())
    {
        qDebug()<<q.lastError().text();
        return false;
    }
    else
    {
        userId = q.value(0).toInt();
        return true;
    }
}

QVector<QStringList> Sql_Mgr::getUser(QString strCondition)
{
    QSqlQuery q(m_Db);
    QString strSql = QString("select * from user where username like '%%1%'").arg(strCondition);

    //qDebug()<<strSql;

    QVector<QStringList> vec;
    bool ret = q.exec(strSql);
    if(!ret)
    {
        qDebug()<<q.lastError().text();
    }
    else
    {
        int iCols = q.record().count();

        QStringList l;
        while (q.next()) {
            l.clear();
            for(int i = 0;i<iCols; i++)
            {
                l<<q.value(i).toString();
            }
            vec.push_back(l);
            q.value(0);
        }
    }
    return vec;
}

void Sql_Mgr::addUser(QVector<QStringList> v)
{
    m_Db.transaction();
    QSqlQuery q(m_Db);
    for(auto it:v)
    {
        QString strSql = QString("insert into user values(NULL,'%1','%2','%3','%4','%5','%6')")
                             .arg(it[0])
                             .arg(it[1])
                             .arg(it[2])
                             .arg(it[3])
                             .arg(it[4])
                             .arg(it[5]);
        bool ret = q.exec(strSql);
        if(!ret)
        {
            qDebug()<<q.lastError().text();
        }
    }
    m_Db.commit();
}

void Sql_Mgr::delUser(QString strId)
{
    QSqlQuery q(m_Db);
    QString strSql = QString("delete from user where userid = '%1'").arg(strId);

    bool ret = q.exec(strSql);
    if(!ret)
    {
        qDebug()<<q.lastError().text();
    }
}

QVector<QStringList> Sql_Mgr::getBook(QString strCondition, int id)
{
    QSqlQuery q(m_Db);
    QString strSql;
    if(-1==id)
    {
        strSql = QString("select * from book where name like '%%1%'").arg(strCondition);
    }
    else
    {
        strSql = QString("select * from book where bookid='%1'").arg(id);
    }

    //qDebug()<<strSql;

    QVector<QStringList> vec;
    bool ret = q.exec(strSql);
    if(!ret)
    {
        qDebug()<<q.lastError().text();
    }
    else
    {
        int iCols = q.record().count();

        QStringList l;
        while (q.next()) {
            l.clear();
            for(int i = 0;i<iCols; i++)
            {
                l<<q.value(i).toString();
            }
            vec.push_back(l);
            q.value(0);
        }
    }
    return vec;
}

void Sql_Mgr::addBooks(QVector<QStringList> v)
{
    if(0 == v.size())
    {
        return;
    }

    auto ldata = v[0];
    QSqlQuery q(m_Db);
    QString strSql = QString("insert into book values(null,'%1','%2','%3','%4','%5','%6','')")
                         .arg(ldata[1])
                         .arg(ldata[2])
                         .arg(ldata[3])
                         .arg(ldata[4])
                         .arg(ldata[5])
                         .arg(ldata[6]);
    bool ret = q.exec(strSql);
    if(!ret)
    {
        qDebug()<<q.lastError().text();
    }
    
}

void Sql_Mgr::updateBook(QStringList v)
{
    QSqlQuery q(m_Db);

    QString strSql = QString("update book set name='%1',press='%2',type1='%3',type2='%4',type3='%5',cnt='%6' where bookid='%7'")
                         .arg(v[1])
                         .arg(v[2])
                         .arg(v[3])
                         .arg(v[4])
                         .arg(v[5])
                         .arg(v[6])
                         .arg(v[0]);
    bool ret = q.exec(strSql);
    if(!ret)
    {
        qDebug()<<q.lastError().text();
    }

}



QString Sql_Mgr::delBook(QString strId)
{
    QString strRet;
    QSqlQuery q(m_Db);
    QString strSql = QString("delete from book where bookid = '%1';").arg(strId);

    bool ret = q.exec(strSql);
    if(!ret)
    {
        qDebug()<<q.lastError().text();
        strRet = "删除失败，请检查";
    }

    return strRet;

}

QString Sql_Mgr::returnBook(QString strUserId, QString strBookId)
{
    QSqlQuery q(m_Db);
    QString strSql = QString("update book set cnt= cnt+1 where bookid='%1';")
                         .arg(strBookId);
    //qDebug()<<strSql;
    bool ret = q.exec(strSql);
    if(!ret)
    {
        qDebug()<<q.lastError().text();
    }

    strSql = QString("delete from record where bookid='%1' and userid='%2';")
                 .arg(strBookId)
                 .arg(strUserId);
    //qDebug()<<strSql;
    ret = q.exec(strSql);
    if(!ret)
    {
        qDebug()<<q.lastError().text();
    }
    return QString("");
}

QString Sql_Mgr::borrowBook(QString strUserId, QString strBookId)
{
    //实现图书借阅
    QSqlQuery q(m_Db);
    QString strSql = QString("update book set cnt= cnt-1 where bookid='%1';")
                         .arg(strBookId);
    //qDebug()<<strSql;
    bool ret = q.exec(strSql);
    if(!ret)
    {
        qDebug()<<q.lastError().text();
    }

    strSql = QString("insert into record values(null,'%1','%2','%3','%4');")
                         .arg(strUserId)
                         .arg(strBookId)
                         .arg(QDateTime::currentSecsSinceEpoch())
                         .arg(QDateTime::currentSecsSinceEpoch() + 3600*10*24);
    //qDebug()<<strSql;
    ret = q.exec(strSql);
    if(!ret)
    {
        qDebug()<<q.lastError().text();
    }

    return QString("");
}

QVector<QStringList> Sql_Mgr::getRecord(QString strCondition)
{
    QSqlQuery q(m_Db);
    QString strSql = QString("select * from record join user using(userid) join book using(bookid) %1").arg(strCondition);

    //qDebug()<<strSql;

    QVector<QStringList> vec;
    bool ret = q.exec(strSql);
    if(!ret)
    {
        qDebug()<<q.lastError().text();
    }
    else
    {
        int iCols = q.record().count();

        QStringList l;
        while (q.next()) {
            l.clear();
            for(int i = 0;i<iCols; i++)
            {
                l<<q.value(i).toString();
            }
            vec.push_back(l);
            q.value(0);
        }
    }
    return vec;
}

void Sql_Mgr::clearRecord()
{
    QSqlQuery q(m_Db);
    QString strSql = QString("delete from book; delete from sqlite_sequence where name = 'book';");

    //qDebug()<<strSql;

    //QVector<QStringList> vec;
    bool ret = q.exec(strSql);
    if(!ret)
    {
        qDebug()<<q.lastError().text();
    }
}


